﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

namespace sistemaAcademicoFinanceiroEscolas
{
    public partial class efetivarPagamentoAlunov2 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                // Carregar alunos existentes e ativos

                SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
                sqlc.Open();

                string sqlAluno = "select * from aluno order by nomeAluno";

                SqlCommand cmdAluno = new SqlCommand(sqlAluno, sqlc);
                SqlDataReader myReaderAluno = cmdAluno.ExecuteReader();

                ListItem AlunoSelecione = new ListItem();
                AlunoSelecione.Value = "0";
                AlunoSelecione.Text = "Selecione um Aluno";

                lstAluno.Items.Add(AlunoSelecione);

                if (myReaderAluno.HasRows)
                {

                    while (myReaderAluno.Read())
                    {
                        ListItem Aluno = new ListItem();

                        Aluno.Value = myReaderAluno["codigoAluno"].ToString();
                        Aluno.Text = myReaderAluno["nomeAluno"].ToString();

                        lstAluno.Items.Add(Aluno);
                    }

                }
                //Aluno carregados
                myReaderAluno.Close();
                cmdAluno.Dispose();

                // fechando conexão com o banco de dados
                sqlc.Close();
            }
        }


        protected void btnListar_Click(object sender, EventArgs e)
        {
            //verificar se o aluno possui algum desconto. 

            string sqlPossuiValorEspecial = "select count(*) as total from valorEspecialAluno where codigoAluno = " + lstAluno.SelectedValue;

            SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
            sqlc.Open();

            SqlCommand cmdAluno = new SqlCommand(sqlPossuiValorEspecial, sqlc);
            SqlDataReader myReaderAluno = cmdAluno.ExecuteReader();

            Boolean possuiValorEspecial = false;

            if (myReaderAluno.HasRows)
            {
                possuiValorEspecial = true;
            }

            cmdAluno.Dispose();
            myReaderAluno.Close();
            sqlc.Close();

            listar(lstAluno.SelectedValue, possuiValorEspecial);
        }

        void listar(string codigoAluno, Boolean possuiValorEspecial)
        {
            string conteudo = "";

            conteudo = "<table><tr><td width=\"50%\"><table border=\"1\" cellpadding=\"5\" cellspacing=\"0\" bordercolor=\"#99CCFF\">";
            conteudo += "<tr><td colspan=4 align=center>Aulas</td></tr><tr>";
            conteudo += "<th>Aluno</th>";
            conteudo += "<th>Data da aula</th>";
            conteudo += "<th>Horário</th>";
            conteudo += "<th>Valor</th>";
            //conteudo += "<th>Pago?</th>";
            conteudo += "</tr>";


            SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
            sqlc.Open();

            SqlConnection sqlf = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
            sqlf.Open();

            //string sql = "SELECT codigoAula, codigoProfessor, (select nomeProfessor from professor where aula.codigoProfessor = professor.codigoProfessor) as nomeProfessor, ";
            //sql += "codigoAluno, (select nomeAluno from aluno where aula.codigoAluno = aluno.codigoAluno) as nomeAluno, codigoGrupo, dataAula, horaInicialAula, ";
            //sql += "horaFinalAula, codigoDisciplina, descricaoAssuntoAula, aulaRealizada, observacoesAula,  ";
            //sql += "aulaRealizada, valorHora, pago, aulaCancelada, valorPago, valorTotalSemDesconto, valorTotalComDesconto, codigoAnoLetivo, falta, meiaHora ";
            //sql += "FROM aula WHERE (codigoAluno = " + lstAluno.SelectedValue + ") and (CONVERT(INT, SUBSTRING(dataAula,7 , 4)) = '2012') order by CONVERT(INT, SUBSTRING(horaInicialAula,1 , 2))";

            string sql = "SELECT a.codigoAula, a.codigoProfessor, a.codigoTipoAula, ";
            sql += "(select nomeProfessor from professor where a.codigoProfessor = professor.codigoProfessor) as nomeProfessor, ";

            sql += "(select nomeAluno from aluno where a.codigoAluno = aluno.codigoAluno) as nomeAluno, ";

            sql += "a.codigoAluno, a.codigoGrupo, a.dataAula, a.horaInicialAula, a.horaFinalAula, a.codigoDisciplina, a.descricaoAssuntoAula, ";
            sql += "a.aulaRealizada, a.observacoesAula, a.valorHora, a.pago, a.aulaCancelada, a.valorPago, ";
            sql += "a.valorTotalSemDesconto, a.valorTotalComDesconto, a.codigoAnoLetivo, a.falta, a.meiaHora, ";

            sql += "(select v.aulaValor from valorAula v ";
            sql += "where convert(datetime,a.dataAula,103) BETWEEN convert(datetime, v.dataInicial,103) ";
            sql += "AND convert(datetime, v.dataFinal,103) and a.codigoTipoAula = v.codigoTipoAula) as valorAula, ";

            sql += "(select v.valorValorEspecialAluno from valorEspecialAluno v ";
            sql += "where convert(datetime,a.dataAula,103) BETWEEN convert(datetime, v.dataInicialValorEspecialAluno,103) ";
            sql += "AND convert(datetime, v.dataFinalValorEspecialAluno,103) and a.codigoAluno = v.codigoAluno) as valorAulaEspecial ";

            sql += "FROM aula a ";
            sql += "WHERE a.codigoAluno = " + lstAluno.SelectedValue;
            sql += " order by convert(datetime,a.dataAula,103) desc, CONVERT(INT, SUBSTRING(a.horaInicialAula,1 , 2))  ";



            SqlCommand cmdPrincipal = new SqlCommand(sql, sqlc);
            SqlDataReader drPrincipal = cmdPrincipal.ExecuteReader();

            string valorEspecial = "";

            if (drPrincipal.HasRows)
            {
                while (drPrincipal.Read())
                {
                    if (drPrincipal["dataAula"].ToString().Substring(6,4) == "2013"){
                    
                            int x = 0;                    
                    }

                    conteudo += "<tr>";
                    conteudo += "<td align='center'>" + drPrincipal["nomeAluno"].ToString() + "</td>";
                    conteudo += "<td align='center'>" + drPrincipal["dataAula"].ToString() + "</td>";
                    conteudo += "<td align='center'>" + drPrincipal["horaInicialAula"].ToString() + "" + " às " + "" + drPrincipal["horaFinalAula"].ToString() + "</td>";
                    conteudo += "<td align='center'>";

                    //verificar se o aluno possue desconto

                    //if (possuiValorEspecial)
                    //{

                    //    //resgatar o valor especial definido para este aluno entre estas datas

                    //    string sqlValor = "select v.codigoAluno, v.codigoValorEspecialAluno, v.dataInicialValorEspecialAluno, v.dataFinalValorEspecialAluno, ";
                    //    sqlValor += " v.valorValorEspecialAluno as valorEspecialAluno, a.codigoAula, a.codigoAluno, a.dataAula from valorEspecialAluno v,  aula a";
                    //    sqlValor += " where a.codigoAluno = " + codigoAluno + " and a.codigoAula = " + drPrincipal["codigoAula"].ToString();
                    //    sqlValor += " and a.codigoAluno = v.codigoAluno ";
                    //    sqlValor += " and convert(datetime,a.dataAula,103) ";
                    //    sqlValor += " BETWEEN convert(datetime, v.dataInicialValorEspecialAluno,103) ";
                    //    sqlValor += " AND convert(datetime, v.dataFinalValorEspecialAluno,103) ";


                    //    SqlCommand cmdEspecial = new SqlCommand(sqlValor, sqlf);
                    //    SqlDataReader drEspecial = cmdEspecial.ExecuteReader();



                    //    if (drEspecial.HasRows)
                    //    {
                    //        while (drEspecial.Read())
                    //        {
                    //            valorEspecial = drEspecial["valorEspecialAluno"].ToString();
                    //            conteudo += valorEspecial + "</td>";
                    //        }

                    //    }

                    //    cmdEspecial.Dispose();
                    //    drEspecial.Close();

                    //}
                    //else {

                    //    conteudo += drPrincipal["valorAula"].ToString() + "</td>";
                    
                    //}

                    
                    if (drPrincipal["valorAulaEspecial"].ToString().Replace(" ","") != ""){

                        conteudo += drPrincipal["valorAulaEspecial"].ToString() + "</td>";

                    }else{

                        conteudo += drPrincipal["valorAula"].ToString() + "</td>";
                    
                    }

                    //if (drPrincipal["pago"].ToString() == "True")
                    //{
                    //    conteudo += "<td align='center'><input id=\"Radio1\" type=\"radio\" CHECKED NAME=\"rbt" + drPrincipal["codigoAula"].ToString() + "\" value=\"rbt1" + drPrincipal["codigoAula"].ToString() + "\" />Pago";
                    //    conteudo += "<input id=\"Radio2\" type=\"radio\" name=\"rbt" + drPrincipal["codigoAula"].ToString() + "\" value=\"rbt2" + drPrincipal["codigoAula"].ToString() + "\" />Cancelar</td>";


                    //    //conteudo += "<td align='center'><input type=\"checkbox\" name=\"cbx" + drPrincipal["codigoAula"].ToString() + "\" value=\"cbx" + drPrincipal["codigoAula"].ToString() + "\" checked=checked></td>";
                    //}
                    //else if (drPrincipal["aulaCancelada"].ToString() == "True")
                    //{
                    //    conteudo += "<td align='center'><input id=\"Radio1\" type=\"radio\" NAME=\"rbt" + drPrincipal["codigoAula"].ToString() + "\" value=\"rbt1" + drPrincipal["codigoAula"].ToString() + "\" />Pago";
                    //    conteudo += "<input id=\"Radio2\" type=\"radio\" CHECKED name=\"rbt" + drPrincipal["codigoAula"].ToString() + "\" value=\"rbt2" + drPrincipal["codigoAula"].ToString() + "\" />Cancelar</td>";
                    //}
                    //else
                    //{
                    //    conteudo += "<td align='center'><input id=\"Radio1\" type=\"radio\" NAME=\"rbt" + drPrincipal["codigoAula"].ToString() + "\" value=\"rbt1" + drPrincipal["codigoAula"].ToString() + "\" />Pago";
                    //    conteudo += "<input id=\"Radio2\" type=\"radio\" name=\"rbt" + drPrincipal["codigoAula"].ToString() + "\" value=\"rbt2" + drPrincipal["codigoAula"].ToString() + "\" />Cancelar</td>";

                    //}

                    //if (drPrincipal["valorPago"].ToString() != "")
                    //{
                    //    conteudo += "<td align='center'><input type=\"text\" name=\"txt" + drPrincipal["codigoAula"].ToString() + "\" value=\"" + drPrincipal["valorPago"].ToString() + "\"></td>";
                    //}
                    //else
                    //{
                    //    conteudo += "<td align='center'><input type=\"text\" name=\"txt" + drPrincipal["codigoAula"].ToString() + "\"></td>";
                    //}


                    conteudo += "</tr>";
                }
            }

            drPrincipal.Close();

            sqlc.Close();

            sqlf.Close();
            

            conteudo += "</table></td>";
            conteudo += "<td width=\"50%\" valign=\"top\"><table border=\"1\" cellpadding=\"5\" cellspacing=\"0\" bordercolor=\"#99CCFF\">";
            conteudo += "<tr><td colspan=2 align=center>Pagamentos</td></tr><tr>";
            conteudo += "<th>Data do pagamento</th>";
            conteudo += "<th>Valor do pagamento</th>";
            conteudo += "</tr>";


            conteudo += "</td></tr></table>";

            Panel1.Controls.Add(new LiteralControl(conteudo));
        }

        protected void btnSalvar_Click(object sender, EventArgs e)
        {
            string sqlPossuiValorEspecial = "select count(*) as total from valorEspecialAluno where codigoAluno = " + lstAluno.SelectedValue;

            SqlConnection sqle = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
            sqle.Open();

            SqlCommand cmdAluno = new SqlCommand(sqlPossuiValorEspecial, sqle);
            SqlDataReader myReaderAluno = cmdAluno.ExecuteReader();

            Boolean possuiValorEspecial = false;

            if (myReaderAluno.HasRows)
            {
                possuiValorEspecial = true;
            }

            cmdAluno.Dispose();
            myReaderAluno.Close();
            sqle.Close();

            listar(lstAluno.SelectedValue, possuiValorEspecial);
        }

        //protected void btnSalvar_Click(object sender, EventArgs e)
        //{
        //    //código de salvar
        //    //inicio
        //    //---
        //    //mensagem
        //    //fim

        //    SqlConnection sqlc = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
        //    sqlc.Open();
        //    SqlConnection sqld = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
        //    sqld.Open();

        //    string sql = "SELECT codigoAula, codigoProfessor, (select nomeProfessor from professor where aula.codigoProfessor = professor.codigoProfessor) as nomeProfessor, \n";
        //    sql += "codigoAluno, (select nomeAluno from aluno where aula.codigoAluno = aluno.codigoAluno) as nomeAluno, codigoGrupo, dataAula, horaInicialAula, \n";
        //    sql += "horaFinalAula, codigoDisciplina, descricaoAssuntoAula, aulaRealizada, observacoesAula,  \n";
        //    sql += "aulaRealizada, valorHora, pago, aulaCancelada, valorPago, valorTotalSemDesconto, valorTotalComDesconto, codigoAnoLetivo, falta, meiaHora \n";
        //    sql += "FROM aula WHERE (codigoAluno = " + lstAluno.SelectedValue + ") order by CONVERT(INT, SUBSTRING(horaInicialAula,1 , 2))";

        //    SqlCommand cmdPrincipal = new SqlCommand(sql, sqlc);
        //    SqlDataReader drPrincipal = cmdPrincipal.ExecuteReader();

        //    if (drPrincipal.HasRows)
        //    {
        //        while (drPrincipal.Read())
        //        {
        //            string cbx = "cbx" + drPrincipal["codigoAula"].ToString();
        //            string txt = "txt" + drPrincipal["codigoAula"].ToString();
        //            string valorTXT = Request["rbt" + drPrincipal["codigoAula"].ToString()];
        //            string valorCBX = Request["rbt" + drPrincipal["codigoAula"].ToString()];

        //            string update = "update aula set aulaCancelada=0, pago=0, valorPago='' where codigoAula = " + drPrincipal["codigoAula"].ToString();

        //            SqlCommand cmd = new SqlCommand(update, sqld);
        //            cmd.ExecuteNonQuery();

        //            if (Request["rbt" + drPrincipal["codigoAula"].ToString()] == "rbt1" + drPrincipal["codigoAula"].ToString())
        //            {
        //                //if (Request["txt" + drPrincipal["codigoAula"].ToString()] != "")
        //                //{
        //                update = "update aula set pago=1, valorPago='" + drPrincipal["valorHora"].ToString() + "' where codigoAula = " + drPrincipal["codigoAula"].ToString();

        //                cmd = new SqlCommand(update, sqld);
        //                cmd.ExecuteNonQuery();
        //                //}
        //            }
        //            else if (Request["rbt" + drPrincipal["codigoAula"].ToString()] == "rbt2" + drPrincipal["codigoAula"].ToString())
        //            {
        //                update = "update aula set aulaCancelada=1, valorPago='" + drPrincipal["valorHora"].ToString() + "' where codigoAula = " + drPrincipal["codigoAula"].ToString();

        //                cmd = new SqlCommand(update, sqld);
        //                cmd.ExecuteNonQuery();
        //            }
        //        }
        //    }

        //    drPrincipal.Close();

        //    sqlc.Close();
        //    sqld.Close();

        //    string literalcc = "<script language=JavaScript>finalizar();</script>";
        //    Panel1.Controls.Add(new LiteralControl(literalcc));

        //    string sqlPossuiValorEspecial = "select count(*) as total from valorEspecialAluno where codigoAluno = " + lstAluno.SelectedValue;

        //    SqlConnection sqle = new SqlConnection(ConfigurationManager.AppSettings["baseDados"]);
        //    sqle.Open();

        //    SqlCommand cmdAluno = new SqlCommand(sqlPossuiValorEspecial, sqle);
        //    SqlDataReader myReaderAluno = cmdAluno.ExecuteReader();

        //    Boolean possuiValorEspecial = false;

        //    if (myReaderAluno.HasRows)
        //    {
        //        possuiValorEspecial = true;
        //    }

        //    cmdAluno.Dispose();
        //    myReaderAluno.Close();
        //    sqlc.Close();

        //    listar(lstAluno.SelectedValue, possuiValorEspecial);
        //}
    }
}
